using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Common;
using MySql.Data.MySqlClient;
using System.Data;
using System.Web.UI.WebControls;
using System.Web.Configuration;
using System.Configuration;
using System.Text;
using System.Data.SqlClient;

/// <summary>
/// An abstract database connection which can connect to different server types
/// </summary>
public class Database {
    public enum DatabaseType {
        [EnumBinder.EnumDescription("MySQL")]
        MySQL,

        [EnumBinder.EnumDescription("MS SQL Server")]
        SQLServer,

        SQLite
    }

    private DbConnection conn;
    private DatabaseType type;
    private String tablePrefix;

    /// <summary>
    /// Gets or sets the type of this database instance.
    /// </summary>
    /// <value>The type of the instance.</value>
    public DatabaseType InstanceType {
        get { return type; }
        set { type = value; }
    }

    /// <summary>
    /// Gets a value indicating whether the connection is open.
    /// </summary>
    /// <value><c>true</c> if connected, otherwise <c>false</c>.</value>
    public bool Connected {
        get {
            if(conn != null && conn.State == ConnectionState.Open)
                return true;
            else
                return false;
        }
    }

    /// <summary>
    /// Gets a string describing this database connection.
    /// </summary>
    /// <value>The name of the nice.</value>
    public string NiceName {
        get {
            if(this.Connected) {
                if(this.type == DatabaseType.SQLite) {
                    return string.Format("{0} ({1})", WebConfigurationManager.AppSettings["dbpath"], this.type);
                } else {
                    return string.Format("{0}:{1} ({2})", WebConfigurationManager.AppSettings["dbhost"], WebConfigurationManager.AppSettings["dbport"], this.type);
                }
            } else {
                return Resources.UIStrings.NoDatabaseConnection;
            }
        }
    }

    /// <summary>
    /// Gets the prefix for ResourceBlender tables.
    /// </summary>
    /// <value>The table prefix.</value>
    public string TablePrefix {
        get { return tablePrefix; }
    }

    /// <summary>
    /// Initializes a new <see cref="Database"/>.
    /// </summary>
    public Database() {
        ConnectionStringSettings config = WebConfigurationManager.ConnectionStrings["Default"];
        tablePrefix = WebConfigurationManager.AppSettings["tableprefix"];
        switch(config.ProviderName) {
            case "MySql.Data.MySqlClient":
                this.type = DatabaseType.MySQL;
                conn = new MySqlConnection(config.ConnectionString);
                break;
            case "System.Data.SqlClient":
                this.type = DatabaseType.SQLServer;
                conn = new SqlConnection(config.ConnectionString);
                break;
        }
    }

    /// <summary>
    /// Opens a connection.
    /// </summary>
    /// <param name="type">The server type.</param>
    /// <param name="connectionString">The connection string.</param>
    public Database(DatabaseType type, string connectionString, string tablePrefix) {
        this.type = type;
        this.tablePrefix = tablePrefix;
        switch(type) {
            case DatabaseType.MySQL:
                conn = new MySqlConnection(connectionString);
                break;
            case DatabaseType.SQLServer:
                conn = new SqlConnection(connectionString);
                break;
        }
        conn.Open();
    }

    /// <summary>
    /// Checks whether the database configuration appears valid. Ie: Is SQLite mode specified without a DB path, or MySQL/SQL Server Mode without a host or port number.
    /// Throws an exception if the configuration is invalid
    /// </summary>
    /// <exception cref="T:System.ArgumentException">If the host, port or database name are missing</exception>
    public void CheckConfig() {
        if(type == DatabaseType.SQLite) {
            string fileName = WebConfigurationManager.AppSettings["dbpath"].ToString();
            if(fileName.Length == 0 || !System.IO.File.Exists(fileName)) {
                throw new System.IO.FileNotFoundException(string.Format(Resources.Errors.ErrorNumDescription, "460", Resources.Errors.SQLiteDbNotSpecifiedOrFound));
            }
        } else {
            string dbHost = WebConfigurationManager.AppSettings["dbhost"].ToString().Trim(),
                   dbPort = WebConfigurationManager.AppSettings["dbport"].ToString().Trim(),
                   dbDatabase = WebConfigurationManager.AppSettings["dbdatabase"].ToString().Trim();
            if(dbHost.Length == 0 || dbPort.Length == 0 || dbDatabase.Length == 0) {
                throw new ArgumentException(string.Format(Resources.Errors.ErrorNumDescription, "461", Resources.Errors.NoDbCredentials));
            }
        }
    }

    /// <summary>
    /// Opens the connection
    /// </summary>
    public void OpenConnection() {
        if(conn.State != ConnectionState.Open) {
            conn.Open();
        }
    }

    /// <summary>
    /// Opens a connection.
    /// </summary>
    /// <param name="type">The server type.</param>
    /// <param name="connectionString">The connection string.</param>
    public void OpenConnection(DatabaseType type, string connectionString) {
        this.type = type;
        switch(type) {
            case DatabaseType.MySQL:
                conn = new MySqlConnection(connectionString);
                break;
            case DatabaseType.SQLServer:
                conn = new SqlConnection(connectionString);
                break;
        }
        conn.Open();
    }

    /// <summary>
    /// Closes the connection.
    /// </summary>
    public void CloseConnection() {
        conn.Close();
    }

    /// <summary>
    /// Checks whether the ResourceBlender tables exist in the database.
    /// </summary>
    /// <returns></returns>
    public bool TablesExist() {
        bool exist = false;
        using(Database.Command cmd = CreateCommand()) {
            DataRow[] rows = null;
            switch(this.type) {
                case DatabaseType.MySQL:
                    cmd.CommandText = string.Format("SHOW TABLES LIKE '{0}%'", TablePrefix);
                    using(DbDataReader reader = cmd.ExecuteReader()) {
                        exist = reader.HasRows;
                        reader.Close();
                    }
                    break;
                case DatabaseType.SQLServer:
                    cmd.CommandText = string.Format("IF OBJECT_ID('dbo.{0}applications', 'U') IS NOT NULL SELECT 'true' ELSE SELECT 'false'", tablePrefix);
                    exist = Convert.ToBoolean(cmd.ExecuteScalar());
                    break;
                case DatabaseType.SQLite:
                    rows = conn.GetSchema("Tables").Select(string.Format("Table_Name LIKE '{0}%'", tablePrefix));
                    exist = rows != null && rows.Length > 0;
                    break;
            }
        }
        return exist;
    }

    /// <summary>
    /// Creates a database command.
    /// </summary>
    /// <returns></returns>
    public Command CreateCommand() {
        return new Command(type, conn.CreateCommand());
    }

    /// <summary>
    /// Creates a database command and assigns it to a transaction.
    /// </summary>
    /// <returns></returns>
    public Command CreateCommand(DbTransaction trans) {
        return new Command(type, conn.CreateCommand(), trans);
    }

    /// <summary>
    /// Begins a transaction.
    /// </summary>
    /// <returns></returns>
    public DbTransaction BeginTransaction() {
        return conn.BeginTransaction();
    }

    /// <summary>
    /// Creates the tables needed by ResourceBlender.
    /// </summary>
    /// <param name="applications"><c>true</c> if the applications table should be created.</param>
    /// <param name="elements"><c>true</c> if the elements table should be created.</param>
    /// <param name="resources"><c>true</c> if the resources table should be created.</param>
    /// <param name="resourceComments"><c>true</c> if the resource comments table should be created.</param>
    /// <param name="users"><c>true</c> if the users table should be created.</param>
    /// <param name="templates"><c>true</c> if the templates table should be created.</param>
    public void CreateTables(bool applications, bool elements, bool resources, bool resourceComments, bool users, bool templates) {
        if(conn == null) {
            throw new InvalidOperationException(Resources.UIStrings.ConnectionNotInitialized);
        } else {
            if(conn.State != ConnectionState.Open) {
                OpenConnection();
            }
            using(Command cmd = CreateCommand()) {
                StringBuilder sqlBuffer = new StringBuilder();
                switch(type) {
                    case DatabaseType.MySQL:
                        if(applications) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE IF NOT EXISTS `{0}applications` (
                                          `id` varchar(10) NOT NULL default '',
                                          `displayname` varchar(255) NOT NULL,
                                          `name` varchar(255) NOT NULL,
                                          `defaultlanguage` varchar(7) NOT NULL,
                                          `languages` text NOT NULL,
                                          PRIMARY KEY  (`id`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;", TablePrefix));
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0, sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                            sqlBuffer.Append(string.Format(@"CREATE TABLE IF NOT EXISTS `{0}bundles` (
                                          `id` varchar(10) NOT NULL,
                                          `name` varchar(255) NOT NULL,
                                          `application` varchar(10) NOT NULL,
                                          PRIMARY KEY  (`id`),
                                          UNIQUE KEY `unique_bundle` (`name`,`application`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;", TablePrefix));
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0, sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(elements) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE IF NOT EXISTS `{0}elements` (
                                          `id` int(11) NOT NULL auto_increment,
                                          `applicationid` varchar(10) NOT NULL,
                                          `bundle` varchar(10) NOT NULL,
                                          `elementname` varchar(255) NOT NULL,
                                          `resourceid` int(10) default NULL,
                                          `comment` text,
                                          PRIMARY KEY  (`id`),
                                          UNIQUE KEY `uniqueelement` (`applicationid`,`elementname`,`bundle`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8", TablePrefix));
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0, sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(resources) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE IF NOT EXISTS `{0}resources` (
                                          `resourceid` int(10) NOT NULL default '0',
                                          `language` varchar(10) NOT NULL,
                                          `datatype` varchar(255),
                                          `mimetype` varchar(255),
                                          `translation` text NOT NULL,
                                          `translatorcomment` text,
                                          PRIMARY KEY  (`resourceid`,`language`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;", TablePrefix));
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0, sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(resourceComments) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE IF NOT EXISTS `{0}resourcecomments` (
                                          `resourceid` int(10) NOT NULL,
                                          `comment` text,
                                          PRIMARY KEY  (`resourceid`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;", TablePrefix));
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0, sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(users) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE IF NOT EXISTS `{0}users` (
                                          `id` int(255) NOT NULL auto_increment,
                                          `username` varchar(255) NOT NULL,
                                          `password` varchar(255) NOT NULL,
                                          `email` text,
                                          `permissions` int(11) NOT NULL default '0',
                                          `allowedprojects` text,
                                          PRIMARY KEY  (`id`),
                                          UNIQUE KEY `unique_username` (`username`)
                                        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;", TablePrefix));
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0, sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(templates) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE IF NOT EXISTS `{0}templates` (
                                              `id` int(5) NOT NULL auto_increment,
                                              `name` varchar(255) NOT NULL,
                                              `filename` varchar(255) NOT NULL,
                                              `filestart` text NOT NULL,
                                              `line` varchar(255) NOT NULL,
                                              `fileend` text NOT NULL,
                                              PRIMARY KEY  (`id`),
                                              UNIQUE KEY `unique_name` (`name`)
                                            ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;", TablePrefix));
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0, sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                            sqlBuffer.Append(string.Format(@"INSERT IGNORE INTO `{0}templates` (`name`, `filename`, `filestart`, `line`, `fileend`) VALUES ", TablePrefix))
                                     .Append(@"('PHP Definitions', '{=LanguageCode}_{=Bundle}.php', '<?php', 'define(""{=Element}"",""{=Translation}"");', '?>'),
                                     		   ('PHP Array', '{=Bundle}_{=LanguageCode}.php', '<?php\r\n${=Bundle} = array(', '""{=Element}"" => ""{=Translation}"",', '); ?>');");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0, sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }
                        break;
                    case DatabaseType.SQLServer:
                        if(applications) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}applications](
	                                        [id] [varchar](10) NOT NULL,
	                                        [displayname] [nvarchar](max) NOT NULL,
	                                        [name] [nvarchar](max) NOT NULL,
	                                        [defaultlanguage] [varchar](7) NOT NULL,
	                                        [languages] [varchar](max) NOT NULL
                                        CONSTRAINT [PK_{0}applications] PRIMARY KEY CLUSTERED ([id] ASC))", TablePrefix));
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0, sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                            sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}bundles] (
                                            [id] [varchar](10) NOT NULL,
	                                        [name] [nvarchar](200) NOT NULL,
                                            [application] [varchar](10) NOT NULL,
                                        CONSTRAINT [PK_{0}bundle] PRIMARY KEY CLUSTERED ([id] ASC),
                                        CONSTRAINT [unique_{0}bundle] UNIQUE NONCLUSTERED ([name] ASC,[application] ASC))", TablePrefix));
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0, sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(elements) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}elements](
	                                        [id] [int] IDENTITY(1,1) NOT NULL,
	                                        [applicationid] [varchar](10) NOT NULL,
	                                        [bundle] [varchar](10) NOT NULL,
	                                        [elementname] [nvarchar](255) NOT NULL,
	                                        [resourceid] [int] NULL,
	                                        [comment] [nvarchar](max) NULL,
                                        CONSTRAINT [PK_{0}element] PRIMARY KEY CLUSTERED ([id] ASC),
                                        CONSTRAINT [ix_{0}uniqueelement] UNIQUE NONCLUSTERED ([applicationid] ASC,[bundle] ASC,[elementname] ASC))", TablePrefix));
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0, sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(resources) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}resources](
	                                        [resourceid] [int] NOT NULL,
	                                        [language] [varchar](10) NOT NULL,
                                            [datatype] [nvarchar](255),
                                            [mimetype] [nvarchar](255),
	                                        [translation] [nvarchar](max) NOT NULL,
                                            [translatorcomment] nvarchar(max) NULL,
                                        CONSTRAINT [PK_{0}resources] PRIMARY KEY CLUSTERED ([resourceid] ASC, [language] ASC))", TablePrefix));
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0, sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(resourceComments) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}resourcecomments](
	                                        [resourceid] [int] NOT NULL,
	                                        [comment] [nvarchar](max) NULL,
                                        CONSTRAINT [PK_{0}resourcecomments] PRIMARY KEY CLUSTERED ([resourceid] ASC))", TablePrefix));
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0, sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(users) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}users](
	                                        [id] [int] IDENTITY(1,1) NOT NULL,
	                                        [username] [nvarchar](100) NOT NULL,
	                                        [password] [nvarchar](100) NOT NULL,
	                                        [email] [nvarchar](100) NULL,
	                                        [permissions] [int] NOT NULL,
	                                        [allowedprojects] [nvarchar](max) NULL,
                                        CONSTRAINT [PK_{0}users] PRIMARY KEY CLUSTERED ([id] ASC),
                                        CONSTRAINT [ix_{0}uniqueusername] UNIQUE NONCLUSTERED ([username] ASC))", TablePrefix));
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0, sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }

                        if(templates) {
                            sqlBuffer.Append(string.Format(@"CREATE TABLE [dbo].[{0}templates] (
                                              [id] [int] IDENTITY(10,1) NOT NULL,
                                              [name] [nvarchar](100) NOT NULL,
                                              [filename] [nvarchar](max) NOT NULL,
                                              [filestart] [nvarchar](max) NOT NULL,
                                              [line] [nvarchar](max) NOT NULL,
                                              [fileend] [nvarchar](max) NOT NULL,
                                        CONSTRAINT [PK_{0}templates] PRIMARY KEY CLUSTERED ([id] ASC),
                                        CONSTRAINT [ix_{0}templatename] UNIQUE NONCLUSTERED ([name] ASC))", TablePrefix));
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0, sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                            sqlBuffer.Append(string.Format("INSERT INTO [dbo].[{0}templates] ([name], [filename], [filestart], [line], [fileend]) VALUES ", TablePrefix))
                                     .Append("('PHP Definitions', '{=LanguageCode}_{=Bundle}.php', '<?php', 'define(\"{=Element}\",\"{=Translation}\");', '?>');")
                                     .Append(string.Format("INSERT INTO [dbo].[{0}templates] ([name], [filename], [filestart], [line], [fileend]) VALUES ", TablePrefix))
                                     .Append("('PHP Array', '{=Bundle}_{=LanguageCode}.php', '<?php ${=Bundle} = array(', '\"{=Element}\" => \"{=Translation}\",', '); ?>');");
                            cmd.CommandText = sqlBuffer.ToString();
                            sqlBuffer.Remove(0, sqlBuffer.Length);
                            cmd.ExecuteNonQuery();
                        }
                        break;
                }
            }
        }
    }

    /// <summary>
    /// Drops all tables created by ResourceBlender.
    /// </summary>
    /// <param name="applications"><c>true</c> if the applications table should be dropped.</param>
    /// <param name="elements"><c>true</c> if the elements table should be dropped.</param>
    /// <param name="resources"><c>true</c> if the resources table should be dropped.</param>
    /// <param name="resourceComments"><c>true</c> if the resource comments table should be dropped.</param>
    /// <param name="users"><c>true</c> if the users table should be dropped.</param>
    public void DropTables(bool applications, bool elements, bool resources, bool resourceComments, bool users, bool templates) {
        if(conn == null) {
            throw new InvalidOperationException(Resources.UIStrings.ConnectionNotInitialized);
        } else {
            if(conn.State != ConnectionState.Open) {
                OpenConnection();
            }
            using(Command cmd = CreateCommand()) {
                if(applications) {
                    cmd.CommandText = "DROP TABLE " + tablePrefix + "applications;";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "DROP TABLE " + tablePrefix + "bundles;";
                    cmd.ExecuteNonQuery();
                }
                if(elements) {
                    cmd.CommandText = "DROP TABLE " + tablePrefix + "elements";
                    cmd.ExecuteNonQuery();
                }
                if(resources) {
                    cmd.CommandText = "DROP TABLE " + tablePrefix + "resources";
                    cmd.ExecuteNonQuery();
                }
                if(resourceComments) {
                    cmd.CommandText = "DROP TABLE " + tablePrefix + "resourcecomments";
                    cmd.ExecuteNonQuery();
                }
                if(templates) {
                    cmd.CommandText = "DROP TABLE " + tablePrefix + "templates";
                    cmd.ExecuteNonQuery();
                }
                if(users) {
                    cmd.CommandText = "DROP TABLE " + tablePrefix + "users";
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }

    /// <summary>
    /// Adds a sample application and some resources to show basic functionality.
    /// </summary>
    public void AddDummyData() {
        if(conn == null) {
            throw new InvalidOperationException(Resources.UIStrings.ConnectionNotInitialized);
        } else {
            if(conn.State != ConnectionState.Open) {
                OpenConnection();
            }
            using(Database.Command cmd = CreateCommand()) {
                StringBuilder sqlBuffer = new StringBuilder();
                switch(this.InstanceType) {
                    case DatabaseType.MySQL:
                        cmd.CommandText = string.Format(@"INSERT IGNORE INTO `{0}applications` (id, displayname, name, defaultlanguage, languages) VALUES ('HJ1556', 'Sample App', 'sampleapp', 'en-US', 'en-US,de-DE,zh-TW');", TablePrefix);
                        cmd.ExecuteNonQuery();

                        cmd.CommandText = string.Format(@"INSERT IGNORE INTO `{0}bundles` (id, name, application) VALUES ('L49181', 'bundle1', 'HJ1556'), ('L49270', 'bundle2', 'HJ1556');", TablePrefix);
                        cmd.ExecuteNonQuery();

                        sqlBuffer.Append(string.Format(@"INSERT IGNORE INTO `{0}elements` (applicationid, bundle, elementname, resourceid) VALUES ", TablePrefix))
                             .Append(@"('HJ1556','L49181','TranslateMe',20000),('HJ1556','L49181','WinTitle',20002),('HJ1556','L49181','ImAnElement',20004),")
                             .Append(@"('HJ1556','L49270','AboutResourceBlender',20001),('HJ1556','L49270','CloseButtonText',20003),('HJ1556','L49270','HTMLEncodedLink',20005);");
                        cmd.CommandText = sqlBuffer.ToString();
                        sqlBuffer.Remove(0, sqlBuffer.Length);
                        cmd.ExecuteNonQuery();

                        sqlBuffer.Append(string.Format(@"INSERT IGNORE INTO `{0}resources` (resourceid, language, translation,translatorcomment) VALUES ", TablePrefix))
                             .Append(@"(20000,'en-US','Translate Me!',''),
	                                    (20001,'en-US','ResourceBlender allows you to translate literal strings then export them to different formats.',''),
	                                    (20002,'en-US','Translation Example','An example translation'),
	                                    (20003,'en-US','Close',''),
	                                    (20004,'en-US','Im a resource','');");
                        //(20004,'en-US',N'Iñtërnâtiônàlizætiøn','Mixed Euro');");
                        cmd.CommandText = sqlBuffer.ToString();
                        sqlBuffer.Remove(0, sqlBuffer.Length);
                        cmd.ExecuteNonQuery();
                        break;
                    case DatabaseType.SQLServer:
                        using(SqlCommand sCmd = ((SqlConnection)conn).CreateCommand()) {
                            sCmd.CommandText = string.Format(@"IF NOT EXISTS (SELECT * FROM {0}applications WHERE id = 'HJ1556')
                                                BEGIN
                                                INSERT INTO {0}applications (id, displayname, name, defaultlanguage, languages) VALUES ('HJ1556', 'Sample App', 'sampleapp', 'en-US', 'en-US,de-DE,zh-TW')
                                                INSERT INTO {0}bundles (id, name, application) VALUES ('L49181', 'bundle1', 'HJ1556');
                                                INSERT INTO {0}bundles (id, name, application) VALUES ('L49271', 'bundle2', 'HJ1556');

                                                INSERT INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49181','TranslateMe',20000);
                                                INSERT INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49271','AboutResourceBlender',20001);
                                                INSERT INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49181','WinTitle',20002);
                                                INSERT INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49271','CloseButtonText',20003);
                                                INSERT INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49181','ImAnElement',20004);
                                                INSERT INTO {0}elements (applicationid, bundle, elementname, resourceid) VALUES ('HJ1556','L49271','HTMLEncodedLink',20005);

                                                INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES(20000,'en-US','Translate Me!','');
                                                INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES(20001,'en-US','ResourceBlender allows you to translate literal strings then export them to different formats.','');
                                                INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES(20002,'en-US','Translation Example','An example translation');
                                                INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES(20003,'en-US','Close','');
                                                INSERT INTO {0}resources (resourceid, language, translation, translatorcomment) VALUES(20004,'en-US','Im a resource','')
                                                END", TablePrefix);
                            sCmd.ExecuteNonQuery();
                        }
                        break;
                }
            }
        }
    }

    /// <summary>
    /// Generates an IN clause or an empty string if there are no values.
    /// </summary>
    /// <param name="fieldName">Name of the field.</param>
    /// <param name="values">The values to include.</param>
    /// <param name="useAnd">If <c>true</c>, the clause will be prepended by AND.</param>
    /// <returns>An SQL IN clause</returns>
    /// <example>GenerateInClause("title", new string[] {"ab", "cd"}, true) - <c>AND title IN('ab', 'cd')</c></example>
    /// <example>GenerateInClause("title", new string[] {"ab", "cd"}, false) - <c>title IN('ab', 'cd')</c></example>
    /// <example>GenerateInClause("title", new string[] {}, true) - <c>empty string</c></example>
    public string GenerateInClause(string fieldName, ICollection<string> values, bool useAnd) {
        if(values.Count == 0) {
            return string.Empty;
        } else {
            StringBuilder buffer = new StringBuilder(useAnd ? " AND " : " ");
            buffer.Append(fieldName).Append(" IN('").Append(Utils.Implode(values, "', '", false)).Append("')");
            return buffer.ToString();
        }
    }

    /*        /// <summary>
            /// Generates the crosstab part of a query needed to retrieve columns for the specified languages. Does not include leading or trailing commas.
            /// </summary>
            /// <param name="languages">The languages for which to create columns.</param>
            /// <returns>The crosstab part of a query needed to retrieve columns for the specified languages. Does not include leading or trailing commas.</returns>
            public string GetCrosstabLanguageSql(ObservableSortedDictionary<string, CultureInfo> languages) {
                StringBuilder sqlBuffer = new StringBuilder();
                string crossTabFormat = null;
                switch(InstanceType) {
                    case Database.DatabaseType.MySQL:
                        crossTabFormat = "MAX(IF(language='{0}',translation,null)) AS '{0}',";
                        break;
                    case Database.DatabaseType.SQLServer:
                    case Database.DatabaseType.SQLite:
                        crossTabFormat = "MAX(CASE WHEN language = '{0}' THEN translation ELSE NULL END) AS '{0}',";
                        break;
                }
                foreach(KeyValuePair<string, CultureInfo> language in languages) {
                    sqlBuffer.AppendFormat(crossTabFormat, language.Key);
                }
                sqlBuffer.Remove(sqlBuffer.Length - 1, 1);
                return sqlBuffer.ToString();
            }*/

    /// <summary>
    /// Creates a connection string.
    /// </summary>
    /// <param name="type">The server type.</param>
    /// <param name="host">The host.</param>
    /// <param name="port">The port.</param>
    /// <param name="user">The username.</param>
    /// <param name="pass">The password.</param>
    /// <param name="database">The database name.</param>
    /// <returns></returns>
    public static string GetConnectionString(DatabaseType type, string host, int port, string user, string pass, string database) {
        switch(type) {
            case DatabaseType.MySQL:
                return string.Format(Constants.MySQLConnecionString, host, port, database, user, pass);
            case DatabaseType.SQLServer:
                return string.Format(Constants.SQL2008ConnectionString, host, port, database, user, pass);
        }
        return string.Empty;
    }

    /// <summary>
    /// Gets an SQLite connection string for the specified file path.
    /// </summary>
    /// <param name="dbPath">The path of the database file.</param>
    /// <returns>A connection string for the file</returns>
    public static string GetSQLiteConnectionString(string dbPath) {
        return string.Format(Constants.SQLiteConnectionString, dbPath);
    }

    public class DataAdapter {
        private Command cmd;
        private DbDataAdapter adapter;

        public DataAdapter(Command cmd) {
            this.cmd = cmd;
            switch(cmd.DBType) {
                case DatabaseType.MySQL:
                    this.adapter = new MySqlDataAdapter(cmd.InnerCommand as MySqlCommand);
                    break;
                case DatabaseType.SQLServer:
                    this.adapter = new SqlDataAdapter(cmd.InnerCommand as SqlCommand);
                    break;
            }
        }

        public void Fill(DataSet ds) {
            adapter.Fill(ds);
        }
        public void Fill(DataTable dt) {
            adapter.Fill(dt);
        }
    }

    public class Command : IDisposable {
        public DbCommand InnerCommand { get; set; }
        public DatabaseType DBType { get; set; }

        public DbParameterCollection Parameters {
            get { return InnerCommand.Parameters; }
        }

        public string CommandText {
            get { return InnerCommand.CommandText; }
            set { InnerCommand.CommandText = value; }
        }

        public DbTransaction Transaction {
            get { return InnerCommand.Transaction; }
            set { InnerCommand.Transaction = value; }
        }

        public Command(DatabaseType type, DbCommand cmd) {
            this.DBType = type;
            this.InnerCommand = cmd;
        }

        public Command(DatabaseType type, DbCommand cmd, DbTransaction trans)
            : this(type, cmd) {
            this.Transaction = trans;
        }

        public void AddParameterWithValue(string name, object value) {
            switch(DBType) {
                case DatabaseType.MySQL:
                    (InnerCommand as MySqlCommand).Parameters.AddWithValue(name, value);
                    break;
                case DatabaseType.SQLServer:
                    (InnerCommand as SqlCommand).Parameters.AddWithValue(name, value);
                    break;
            }
        }

        public DbDataReader ExecuteReader() {
            return InnerCommand.ExecuteReader();
        }
        public DbDataReader ExecuteReader(CommandBehavior behaviour) {
            return InnerCommand.ExecuteReader(behaviour);
        }
        /// <summary>
        /// Executes the query and returns the first column of the first row in the result
        /// set returned by the query. All other columns and rows are ignored.
        /// </summary>
        /// <returns>The first column of the first row in the result set.</returns>
        public object ExecuteScalar() {
            return InnerCommand.ExecuteScalar();
        }

        /// <summary>
        /// Executes an SQL statement against a connection object.
        /// </summary>
        /// <returns>The number of rows affected.</returns>
        public int ExecuteNonQuery() {
            return InnerCommand.ExecuteNonQuery();
        }

        #region IDisposable Members
        public void Dispose() {
            InnerCommand.Dispose();
        }
        #endregion
    }
}
